package com.telecom.cms.actions;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.telecom.cms.dao.LOC_LATNDAO;
import com.telecom.cms.dao.SEG_BIZ_ATOM_DEFDAO;
import com.telecom.cms.dbconnect.Changelatn;
import com.telecom.cms.util.Constant;
import com.telecom.cms.dao.GetConnect;
import com.telecom.cms.dto.*;


/**
 * @author yueyingnishang
 * 
 */
public class DoqueryCustAction extends Action {

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.apache.struts.action.Action#execute(org.apache.struts.action.ActionMapping,
	 *      org.apache.struts.action.ActionForm,
	 *      javax.servlet.http.HttpServletRequest,
	 *      javax.servlet.http.HttpServletResponse)
	 */
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		// TODO Auto-generated method stub
		String nowPage = request.getParameter("nowpage");

		List atomlist = (List) request.getSession().getAttribute("AtomList");

		List outputlist = (List) request.getSession()
				.getAttribute("Outputlist");

		if (null == outputlist)
			return mapping.findForward("failure");
		request.setCharacterEncoding("JISAutoDetect");

		String billing_cycle_y = request.getParameter("billing_cycle_y").trim();

		request.setAttribute("billing_cycle_y", billing_cycle_y);

		String billing_cycle_m = request.getParameter("billing_cycle_m").trim();

		request.setAttribute("billing_cycle_m", billing_cycle_m);

		String latnid = ((String) request.getSession().getAttribute(("latnid")))
				.trim();

		String latncd = ((String) request.getSession().getAttribute(("latncd")))
				.trim();

		if (latnid.equals((new Constant()).provinceid)) {
			String latn_id = request.getParameter("selectlatnid");
			if (latn_id == null) {
				latn_id = ((String) request.getSession().getAttribute(
						("selectlatnid"))).trim();
				String latn_cd = ((String) request.getSession().getAttribute(
						("selectlatncd"))).trim();
				latnid = latn_id;
				latncd = latn_cd;
			} else {
				LOC_LATNDTO dto = new LOC_LATNDAO().findById(latn_id);
				String latn_cd = dto.getLatn_cd();
				String latn_desc = dto.getLatn_desc();
				latnid = latn_id;
				latncd = latn_cd;
				request.getSession().setAttribute("selectlatnid", latn_id);
				request.getSession().setAttribute("selectlatndesc", latn_desc);
				request.getSession().setAttribute("selectlatncd", latn_cd);
			}
		}

		String billing_cycle_id = "";

		if (billing_cycle_m.length() == 1) {

			billing_cycle_id = (billing_cycle_y + "0" + billing_cycle_m).trim();
		} else {

			billing_cycle_id = (billing_cycle_y + billing_cycle_m).trim();

		}

		String rssql = "select\n PV_MART_F.BAS_CUST_MONTH.Cust_Id\n ,PV_MART_F.BAS_CUST_MONTH.Cust_Name\n ";
		String endsql = "";
		String tabname = " from \n ";
		List sqllist = new ArrayList();
		List tablist = new ArrayList();
		Connection con = GetConnect.getCsConnection();
		SEG_BIZ_ATOM_DEFDAO dao = new SEG_BIZ_ATOM_DEFDAO(con);

	//	tablist = dao.findCustTabName();
		tablist = null;

		// for (int p = 0; p < tablist.size(); p++) {
		// if (p == 0) {
		// tabname = tabname + (String) tablist.get(p);
		//
		// }
		//
		// else {
		//
		// tabname = tabname + " left join " + (String) tablist.get(p)
		// + " on " + (String) tablist.get(0) + ".Prd_Inst_Id = "
		// + (String) tablist.get(p) + ".Prd_Inst_Id "
		// + " and BILLING_CYCLE_ID = " + billing_cycle_id;
		//
		// // tabname = tabname + "," + (String) tablist.get(p);
		// }
		// }

		tabname = tabname + (String) tablist.get(2) + " \n";

		tabname = tabname + "  left join  " + (String) tablist.get(0) + " \n"
				+ " on " + (String) tablist.get(2) + ".Cust_Id = "
				+ (String) tablist.get(0) + ".Cust_Id  \n and "
				+ (String) tablist.get(0) + ".BILLING_CYCLE_ID = "
				+ billing_cycle_id + "\n" + "  left join  "
				+ (String) tablist.get(1) + " \n" + " on "
				+ (String) tablist.get(2) + ".Cust_Id = "
				+ (String) tablist.get(1) + ".Cust_Id  \n and "
				+ (String) tablist.get(1) + ".BILLING_CYCLE_ID = "
				+ billing_cycle_id + "\n";

		/*
		 * tabname = tabname + " left join (select
		 * prd_inst_id,std_prom_id,prd_prom_stat_id from " + (String)
		 * tablist.get(2) + " where BILLING_CYCLE_ID = " + billing_cycle_id +"
		 * and prd_prom_stat_id=10) tptomtab \n"; tablist.remove(2);
		 * tablist.add("tptomtab"); tabname = tabname + " on " + (String)
		 * tablist.get(1) + ".Prd_Inst_Id = " + (String) tablist.get(2) +
		 * ".Prd_Inst_Id \nand " + (String) tablist.get(2) + ".BILLING_CYCLE_ID = " +
		 * billing_cycle_id + "\n";
		 */
		for (int i = 0; i < outputlist.size(); i++) { // 处理时长金额

			SEG_BIZ_ATOM_DEFDTO dto = (SEG_BIZ_ATOM_DEFDTO) outputlist.get(i);

			sqllist.add(dto);

			if (null == dto.getSRC_TABLE_COL_NAME()
					|| "" == dto.getSRC_TABLE_COL_NAME()) {

				if (dto.getBIZ_ATOM_FLAG().equalsIgnoreCase("money")) {

					rssql = rssql + "," + dto.getBAS_TABLE_NAME().trim() + "."
							+ dto.getBAS_TABLE_COL_NAME().trim() + "/100 as "
							+ dto.getBAS_TABLE_COL_NAME().trim() + "\n";

				} else if (dto.getBIZ_ATOM_FLAG().equalsIgnoreCase("time")) {

					rssql = rssql + "," + dto.getBAS_TABLE_NAME().trim() + "."
							+ dto.getBAS_TABLE_COL_NAME().trim() + "/60 as "
							+ dto.getBAS_TABLE_COL_NAME().trim() + "\n";

				}

				else {
					rssql = rssql + "\n," + dto.getBAS_TABLE_NAME().trim()
							+ "." + dto.getBAS_TABLE_COL_NAME().trim();
				}

			}

			else {

				rssql = rssql + "\n," + dto.getCDE_SRC_TABLE_NAME().trim()
						+ "." + dto.getSRC_TABLE_COL_NAME().trim();

				tabname = tabname + "  \nleft join  "
						+ dto.getCDE_SRC_TABLE_NAME() + " \n on "
						+ dto.getBAS_TABLE_NAME() + "."
						+ dto.getBAS_TABLE_COL_NAME().trim() + " = "
						+ dto.getCDE_SRC_TABLE_NAME() + "."
						+ dto.getBAS_TABLE_COL_NAME().trim();

			}
		}

		for (int k = 0; k < atomlist.size(); k++) {

			ShowatomDTO showdto = (ShowatomDTO) atomlist.get(k);

			SEG_BIZ_ATOM_DEFDTO atomdto = dao.findByName(showdto.getAtomname());

			if ((atomdto.getSHOW_TYPE_ID() == 2 || atomdto.getSHOW_TYPE_ID() == 3)
					&& ((null != showdto.getAtomvalue()) || ("" != showdto
							.getAtomvalue()))) {

				String values = "";

				if (2 == atomdto.getVALUE_TYPE_ID()) {

					String[] valuechar = showdto.getAtomvalue().split(",");

					for (int p = 0; p < valuechar.length; p++) {

						if (p == 0) {

							values = "'" + valuechar[p] + "'";

						} else {
							values = values + "\n,'" + valuechar[p] + "'";
						}
					}

				} else {
					values = showdto.getAtomvalue().substring(0,
							showdto.getAtomvalue().length() - 1);

				}
			} else if (atomdto.getSHOW_TYPE_ID() == 100001 // /处理金额时长
					&& ((null != showdto.getAtomvalue()) || ("" != showdto
							.getAtomvalue()))) {

				String[] va = showdto.getAtomvalue().split(",");
				String bb = "";

				for (int n = 0; n < va.length; n++) {

					String num[] = va[n].split("－");
					for (int i = 0; i < num.length; i++)
						num[i] = num[i].trim();

					if ("money".equalsIgnoreCase(atomdto.getBIZ_ATOM_FLAG()
							.trim())) {

						if (0 == n) {

							bb = "\n(" + atomdto.getBAS_TABLE_NAME() + "."
									+ atomdto.getBAS_TABLE_COL_NAME().trim()
									+ "  between " + Integer.parseInt(num[0])
									* 100 + " and "
									+ (Integer.parseInt(num[1])) * 100 + ")";
						}

						else {

							bb = bb + "\nor" + "("
									+ atomdto.getBAS_TABLE_NAME() + "."
									+ atomdto.getBAS_TABLE_COL_NAME().trim()
									+ "  between " + Integer.parseInt(num[0])
									* 100 + " and "
									+ (Integer.parseInt(num[1])) * 100 + ")";

						}

					} else if ("time".equalsIgnoreCase(atomdto
							.getBIZ_ATOM_FLAG())) {

						if (0 == n) {

							bb = "\n(" + atomdto.getBAS_TABLE_NAME() + "."
									+ atomdto.getBAS_TABLE_COL_NAME().trim()
									+ "  \nbetween " + Integer.parseInt(num[0])
									* 60 + " and " + Integer.parseInt(num[1])
									* 60 + ")";
						}

						else {

							bb = bb + "\nor" + "("
									+ atomdto.getBAS_TABLE_NAME() + "."
									+ atomdto.getBAS_TABLE_COL_NAME().trim()
									+ "  \nbetween " + Integer.parseInt(num[0])
									* 60 + " and " + Integer.parseInt(num[1])
									* 60 + ")";

						}

					} else {
						if (0 == n) {

							bb = "\n(" + atomdto.getBAS_TABLE_NAME() + "."
									+ atomdto.getBAS_TABLE_COL_NAME().trim()
									+ " \nbetween " + Integer.parseInt(num[0])
									+ " and " + Integer.parseInt(num[1]) + ")";
						}

						else {

							bb = bb + "\nor" + "("
									+ atomdto.getBAS_TABLE_NAME() + "."
									+ atomdto.getBAS_TABLE_COL_NAME().trim()
									+ "  \nbetween " + Integer.parseInt(num[0])
									+ " and " + Integer.parseInt(num[1]) + ")";

						}

					}
				}
				endsql = endsql + " \nand " + "(" + bb + ")";

			}

			else if (atomdto.getSHOW_TYPE_ID() == 1
					&& ((null != showdto.getAtomvalue()) || ("" != showdto
							.getAtomvalue()))) {
				String[] cc = showdto.getAtomvalue().split(",");

				endsql = endsql + " \nand " + " ( "
						+ atomdto.getBAS_TABLE_NAME() + "."
						+ atomdto.getBAS_TABLE_COL_NAME().trim()
						+ " \nbetween " + cc[0] + " and " + cc[1] + ")";

			}

			else if (atomdto.getSHOW_TYPE_ID() == 200001
					&& ((null != showdto.getAtomvalue()) || ("" != showdto
							.getAtomvalue()))) {

				endsql = endsql
						+ " \nand "
						+ atomdto.getBAS_TABLE_NAME()
						+ "."
						+ atomdto.getBAS_TABLE_COL_NAME().trim()
						+ "= '"
						+ showdto.getAtomvalue().substring(0,
								showdto.getAtomvalue().length() - 1) + "' ";

			} else if (atomdto.getSHOW_TYPE_ID() == 4) {
				endsql = endsql
						+ " \nand "
						+ "PV_MART_F.BAS_CUST_MONTH."
						+ showdto.getAtomvalue().substring(0,
								showdto.getAtomvalue().length() - 1);
			}

		}

		String besql = rssql + tabname + " where "

		+ endsql.substring(endsql.indexOf("and") + 3) + " \n";

		String sql = Changelatn.changeLatnId(latncd, besql);


		int num = sqllist.size() + 2;

		// /取记录数

		PreparedStatement psnum = null;

		String countsql = null;

		if (rssql.indexOf(" Inv_Amt") == -1) {

			countsql = "select count(1) , sum(tanname.Inv_Amt) , sum(tanname.Irc_Inter_Rgn_Amt) \nfrom  ("
					+ rssql
					+ ",PV_MART_F.BAS_CUST_INV_ALL_MONTH.Inv_Amt/100 as Inv_Amt,PV_MART_F.BAS_CUST_INV_ALL_MONTH.Inter_Rgn_Amt/100 as Irc_Inter_Rgn_Amt "
					+ tabname
					+ "  where "

					+ endsql.substring(endsql.indexOf("and") + 3)
					+ " \n"
					+ " )  tanname";

		}

		else {

			countsql = "select count(1) , sum(tanname.Inv_Amt) , sum(tanname.Irc_Inter_Rgn_Amt) \nfrom  ("
					+ rssql
					+ ",PV_MART_F.BAS_CUST_INV_ALL_MONTH.Inter_Rgn_Amt/100 as Irc_Inter_Rgn_Amt"
					+ tabname
					+ "  where "

					+ endsql.substring(endsql.indexOf("and") + 3)
					+ " \n"
					+ " )  tanname";

		}
		countsql = Changelatn.changeLatnId(latncd, countsql);
		psnum = con.prepareStatement(countsql);
		ResultSet rsnew = psnum.executeQuery();

		int rscount = 0;
		String total = null;
		String inner = null;

		if (rsnew.next()) {

			rscount = rsnew.getInt(1);

			if (rsnew.getString(2) == null) {

				total = "0";
			} else {
				total = rsnew.getString(2);
			}

			if (rsnew.getString(3) == null) {

				inner = "0";
			} else {
				inner = rsnew.getString(3);
			}
		}
		if (rsnew != null) {

			rsnew.close();
		}
		if (psnum != null) {
			psnum.close();

		}

		// 以下拼凑分页sql
		int nowpage = 1;

		if (nowPage == null || new Integer(nowPage).intValue() <= 0) {
			nowpage = 1;
		} else {
			nowpage = (new Integer(request.getParameter("nowpage"))).intValue();
		}

		int recodeNum = nowpage * 20;
		int showNum = 20;
		if (recodeNum > rscount) {
			recodeNum = rscount;
			showNum = rscount % 20;
		}
		List rslist = new ArrayList();
		if (rscount != 0) {
			String recodenum = (new Integer(recodeNum)).toString();
			String shownum = (new Integer(showNum)).toString();
			String divsql = "select * from(select top "
					+ shownum
					+ " * from (select top "
					+ recodenum
					+ " * from("
					+ sql
					+ ")as c Order By Cust_Id)as a Order By Cust_Id desc)as b Order By Cust_Id ";

			// 取样本数据

			PreparedStatement psmt = con.prepareStatement(divsql);

			ResultSet rset = psmt.executeQuery();

			while (rset.next()) {

				String[] desc = new String[num];

				desc[0] = rset.getString(1);
				desc[1] = rset.getString(2);
				// desc[2] = rset.getString(3);
				// desc[3] = rset.getString(4);
				// desc[4] = rset.getString(5);

				for (int cn = 0; cn < sqllist.size(); cn++) {

					if (rset.getObject(cn + 3) == null) {

						desc[cn + 2] = "0";
					} else {
						desc[cn + 2] = rset.getObject(cn + 3).toString();
					}
				}

				rslist.add(desc);

			}

			if (rset != null) {

				rset.close();
			}
			if (psmt != null) {
				psmt.close();

			}
		}
		request.setAttribute("LATNID", latnid);
		request.setAttribute("INNER", inner);
		request.setAttribute("TOTAL", total);
		request.setAttribute("RSLIST", rslist);
		request.getSession().setAttribute("Outputlist", outputlist);
		request.getSession().setAttribute("Sqllist", sqllist);
		request.setAttribute("RSCOUNTNUM", rscount);
		request.getSession().setAttribute("SQL", sql);
		request.setAttribute("nowpage", new Integer(nowpage));
		return mapping.findForward("success");
		// return mapping.findForward("failure");
	}
}
